#!/usr/bin/env python3
"""
Fix remaining NULL team_id records in metrics table
This script handles team name mismatches and updates missing team_id values
"""

import sqlite3
import os
from typing import Dict, Optional

def get_database_path() -> str:
    """Get the path to the SQLite database"""
    current_dir = os.path.dirname(os.path.abspath(__file__))
    data_dir = os.path.join(current_dir, 'data')
    db_path = os.path.join(data_dir, 'uma_insight.db')
    if not os.path.exists(db_path):
        project_root = os.path.dirname(current_dir)
        db_path = os.path.join(project_root, 'backend', 'data', 'uma_insight.db')
    return db_path

def fix_team_name_mismatches():
    """Fix team name mismatches between metric_team_assignments and teams tables"""
    db_path = get_database_path()

    if not os.path.exists(db_path):
        print(f"❌ Database not found at: {db_path}")
        return False

    print(f"🔧 [Fix Team IDs] Processing database: {db_path}")

    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # Get all team name mappings
        cursor.execute("SELECT id, name FROM teams")
        teams = {name: team_id for team_id, name in cursor.fetchall()}
        print(f"📋 [Fix Team IDs] Found {len(teams)} teams in database")

        # Check for team name variations
        team_variations = {}
        for team_name in teams.keys():
            # Check for variations with/without leading/trailing spaces
            team_name_stripped = team_name.strip()
            if team_name_stripped != team_name:
                team_variations[team_name_stripped] = team_name
                print(f"🔍 [Fix Team IDs] Found variation: '{team_name_stripped}' -> '{team_name}'")

        # Get all NULL team_id records with their team assignments
        cursor.execute("""
            SELECT m.id, m.type, mta.team_name
            FROM metrics m
            JOIN metric_team_assignments mta ON m.id = mta.metric_id
            WHERE m.team_id IS NULL
        """)

        null_records = cursor.fetchall()
        print(f"📊 [Fix Team IDs] Found {len(null_records)} metrics with NULL team_id")

        if not null_records:
            print("✅ [Fix Team IDs] No NULL team_id records found")
            return True

        # Group by team name for batch processing
        team_updates = {}
        for metric_id, metric_type, team_name in null_records:
            if team_name not in team_updates:
                team_updates[team_name] = []
            team_updates[team_name].append((metric_id, metric_type))

        updated_count = 0
        errors = []

        for team_name, metrics in team_updates.items():
            # Try to find matching team
            target_team_id = None

            # Direct match
            if team_name in teams:
                target_team_id = teams[team_name]
            # Try stripped variation
            elif team_name in team_variations:
                target_team_id = teams[team_variations[team_name]]

            if target_team_id:
                # Update all metrics for this team
                metric_ids = [m[0] for m in metrics]
                placeholders = ','.join(['?'] * len(metric_ids))

                cursor.execute(f"""
                    UPDATE metrics
                    SET team_id = ?
                    WHERE id IN ({placeholders})
                """, [target_team_id] + metric_ids)

                affected_rows = cursor.rowcount
                updated_count += affected_rows
                print(f"✅ [Fix Team IDs] Updated {affected_rows} metrics for team '{team_name}' -> {target_team_id}")
            else:
                # No matching team found
                metric_types = {}
                for metric_id, metric_type in metrics:
                    metric_types[metric_type] = metric_types.get(metric_type, 0) + 1

                type_summary = ", ".join([f"{t}:{c}" for t, c in metric_types.items()])
                errors.append(f"No matching team found for '{team_name}' (types: {type_summary})")
                print(f"⚠️  [Fix Team IDs] No matching team for '{team_name}'")

        # Commit changes
        conn.commit()

        # Print summary
        print(f"\n📈 [Fix Team IDs] Update Summary:")
        print(f"   Total NULL records found: {len(null_records)}")
        print(f"   Successfully updated: {updated_count}")
        print(f"   Failed to match: {len(errors)}")

        if errors:
            print(f"\n⚠️  [Fix Team IDs] Matching Errors:")
            for error in errors:
                print(f"   - {error}")

        # Verify results
        cursor.execute("SELECT COUNT(*) FROM metrics WHERE team_id IS NULL")
        remaining_null = cursor.fetchone()[0]

        print(f"\n🎯 [Fix Team IDs] Final Result:")
        print(f"   Remaining NULL team_id records: {remaining_null}")

        if remaining_null == 0:
            print("🎉 [Fix Team IDs] All NULL team_id records have been fixed!")
        else:
            print(f"⚠️  [Fix Team IDs] {remaining_null} records still need manual intervention")

        conn.close()
        return remaining_null == 0

    except sqlite3.Error as e:
        print(f"❌ [Fix Team IDs] Database error: {e}")
        return False
    except Exception as e:
        print(f"❌ [Fix Team IDs] Unexpected error: {e}")
        return False

def verify_fix():
    """Verify the fix was successful"""
    db_path = get_database_path()

    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # Get overall statistics
        cursor.execute("""
            SELECT
                COUNT(*) as total_metrics,
                COUNT(team_id) as metrics_with_team_id,
                COUNT(*) - COUNT(team_id) as null_team_ids,
                COUNT(DISTINCT team_id) as unique_teams_assigned
            FROM metrics
        """)

        stats = cursor.fetchone()

        # Get team distribution
        cursor.execute("""
            SELECT t.name, COUNT(m.id) as metric_count
            FROM metrics m
            JOIN teams t ON m.team_id = t.id
            WHERE m.team_id IS NOT NULL
            GROUP BY t.id, t.name
            ORDER BY metric_count DESC
            LIMIT 10
        """)

        team_dist = cursor.fetchall()

        print(f"\n📊 [Verification] Database Statistics:")
        print(f"   Total metrics: {stats[0]}")
        print(f"   Metrics with team_id: {stats[1]}")
        print(f"   NULL team_id records: {stats[2]}")
        print(f"   Unique teams assigned: {stats[3]}")
        print(f"   Coverage: {(stats[1]/stats[0])*100:.1f}%")

        if team_dist:
            print(f"\n🏢 [Verification] Top Teams by Metric Count:")
            for team_name, count in team_dist:
                print(f"   {team_name}: {count} metrics")

        conn.close()
        return stats[2] == 0

    except Exception as e:
        print(f"❌ [Verification] Error: {e}")
        return False

def main():
    """Main function"""
    print("🚀 [Team ID Fix] Starting NULL team_id record fix process")
    print("=" * 60)

    # Fix team name mismatches and update NULL records
    success = fix_team_name_mismatches()

    print("\n" + "=" * 60)

    # Verify the fix
    if success:
        verify_success = verify_fix()
        if verify_success:
            print("🎉 [Team ID Fix] All fixes completed successfully!")
            exit(0)
        else:
            print("⚠️  [Team ID Fix] Fix completed but verification shows remaining issues")
            exit(1)
    else:
        print("❌ [Team ID Fix] Fix process failed")
        exit(1)

if __name__ == "__main__":
    main()